package com.huaxia.dao.realAct;

import com.huaxia.pojo.realAct.BranchClassAct;

import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/08 16:24
 */

public interface BranchClassActMapper {

    @Select("select a.branchsName,a.branchOldAct,b.branchOldNum,round(decode(b.branchOldNum,0,0,a.branchOldAct*100/b.branchOldNum),1)branchOldActYield,c.branchNewAct,d.branchNewNum,round(decode(d.branchNewNum,0,0,c.branchNewAct*100/d.branchNewNum),1)branchNewActYield  from \n" +
            "            (\n" +
            "                    select t.branchsname,sum(t.branchOldAct)branchOldAct from(\n" +
            "                     select sb.branchsname,NVL(a.a,0)branchOldAct from  \n" +
            "                    (select x.saledeptcode,SUM(case when x.YCB>='10000' then 1 end)a FROM\n" +
            "                    ( select a.saledeptcode,NVL(a.cbm,0)-NVL(b.ytm,0) ycb from\n" +
            "                    (select t.saledeptcode, t.AGENTCODE, SUM(WRITTENSTADPREM)cbm from FACT_YX_PREPREM_LIST_day t \n" +
            "                    where TO_CHAR(t.ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and TO_CHAR(t.EMPLOYDATE,'YYYY/MM')!=TO_CHAR(SYSDATE,'YYYY/MM') and t.RNFLAG='N' and t.AGENTSTATE='在职' \n" +
            "                    group by t.AGENTCODE,t.saledeptcode)a left join \n" +
            "                    (select s.saledeptcode, s.AGENTCODE, SUM(WRITTENSTADPREM)ytm from FACT_YX_PREPREM_LIST_day s \n" +
            "                    where TO_CHAR(s.YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and TO_CHAR(s.EMPLOYDATE,'YYYY/MM')!=TO_CHAR(SYSDATE,'YYYY/MM') and s.RNFLAG='N' and s.AGENTSTATE='在职' \n" +
            "                    group by s.AGENTCODE,s.saledeptcode)b on a.AGENTCODE=b.AGENTCODE)x GROUP BY x.saledeptcode)a right join sd_branch2 sb on sb.saledeptcode=a.saledeptcode\n" +
            "                    )t group by t.branchsname\n" +
            "            )a,\n" +
            "            (\n" +
            "                    select t.branchsname,sum(t.branchOldNum)branchOldNum from(\n" +
            "                    select sb.branchsname,NVL(a.branchOldNum,0)branchOldNum from \n" +
            "                    (select d.saledeptcode,count(d.saledeptcode)branchOldNum from D_AGENT_PROVINCE_YX2 d where AGENTSTATE='在职' and TO_CHAR(d.Employdate,'YYYY/MM')!=TO_CHAR(sysdate,'YYYY/MM') group by d.saledeptcode)a\n" +
            "                    right join sd_branch2 sb on sb.saledeptcode=a.saledeptcode\n" +
            "                    )t group by t.branchsname\n" +
            "            )b,\n" +
            "            (\n" +
            "                    select t.branchsname,sum(t.branchNewAct)branchNewAct from(\n" +
            "                    select sb.branchsname,NVL(a.a,0)branchNewAct from  \n" +
            "                    (select x.saledeptcode,SUM(case when x.YCB>='10000' then 1 end)a FROM\n" +
            "                    ( select a.saledeptcode,NVL(a.cbm,0)-NVL(b.ytm,0) ycb from\n" +
            "                    (select t.saledeptcode, t.AGENTCODE, SUM(WRITTENSTADPREM)cbm from FACT_YX_PREPREM_LIST_day t \n" +
            "                    where TO_CHAR(t.ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and TO_CHAR(t.EMPLOYDATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and t.RNFLAG='N' and t.AGENTSTATE='在职' \n" +
            "                    group by t.AGENTCODE,t.saledeptcode)a left join \n" +
            "                    (select s.saledeptcode, s.AGENTCODE, SUM(WRITTENSTADPREM)ytm from FACT_YX_PREPREM_LIST_day s \n" +
            "                    where TO_CHAR(s.YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and TO_CHAR(s.EMPLOYDATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and s.RNFLAG='N' and s.AGENTSTATE='在职' \n" +
            "                    group by s.AGENTCODE,s.saledeptcode)b on a.AGENTCODE=b.AGENTCODE)x GROUP BY x.saledeptcode)a right join sd_branch2 sb on sb.saledeptcode=a.saledeptcode\n" +
            "                    )t group by t.branchsname\n" +
            "             )c,\n" +
            "            (\n" +
            "                    select t.branchsname,sum(t.branchNewNum)branchNewNum from(\n" +
            "                    select sb.branchsname,NVL(a.branchNewNum,0)branchNewNum from \n" +
            "                    (select d.saledeptcode,count(d.saledeptcode)branchNewNum from D_AGENT_PROVINCE_YX2 d where AGENTSTATE='在职' and TO_CHAR(d.Employdate,'YYYY/MM')=TO_CHAR(sysdate,'YYYY/MM') group by d.saledeptcode)a\n" +
            "                    right join sd_branch2 sb on sb.saledeptcode=a.saledeptcode\n" +
            "                    )t group by t.branchsname\n" +
            "            )d \n" +
            "    where a.branchsname=b.branchsname and b.branchsname=c.branchsname and c.branchsname=d.branchsname order by c.branchNewAct DESC")
    List<BranchClassAct> getBranchClassAct();
}
